package dao;

import model.RedWine;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class RedWineDaoImpl implements RedWineDao { // 红酒数据访问实现 完成人：姜凯严 7/3
    private static final String DB_URL = "jdbc:sqlite:redwine.db";

    @Override
    public boolean addRedWine(RedWine redWine) { // 添加红酒
        String sql = "INSERT INTO redwine (name, stock, price, country, city, county) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, redWine.getName());
            pstmt.setInt(2, redWine.getStock());
            pstmt.setDouble(3, redWine.getPrice());
            pstmt.setString(4, redWine.getCountry());
            pstmt.setString(5, redWine.getCity());
            pstmt.setString(6, redWine.getCounty());
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean addRedWine(String name) { // 添加红酒（仅酒名）
        String sql = "INSERT INTO redwine (name, stock, price, country, city, county) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, 0); // 库存默认为0
            pstmt.setDouble(3, 0.0); // 单价默认为0
            pstmt.setString(4, ""); // 国家默认为空
            pstmt.setString(5, ""); // 城市默认为空
            pstmt.setString(6, ""); // 县默认为空
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean addRedWine(String name, int stock, double price) { // 添加红酒（酒名、库存、单价）
        String sql = "INSERT INTO redwine (name, stock, price, country, city, county) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, stock);
            pstmt.setDouble(3, price);
            pstmt.setString(4, ""); // 国家默认为空
            pstmt.setString(5, ""); // 城市默认为空
            pstmt.setString(6, ""); // 县默认为空
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateRedWine(RedWine redWine) { // 修改红酒
        String sql = "UPDATE redwine SET name=?, stock=?, price=?, country=?, city=?, county=? WHERE redwine_id=?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, redWine.getName());
            pstmt.setInt(2, redWine.getStock());
            pstmt.setDouble(3, redWine.getPrice());
            pstmt.setString(4, redWine.getCountry());
            pstmt.setString(5, redWine.getCity());
            pstmt.setString(6, redWine.getCounty());
            pstmt.setInt(7, redWine.getRedWineId());
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateRedWine(int redWineId, int newStock) { // 修改红酒库存
        String sql = "UPDATE redwine SET stock=? WHERE redwine_id=?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, newStock);
            pstmt.setInt(2, redWineId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean deleteRedWine(int redWineId) { // 删除红酒
        String sql = "DELETE FROM redwine WHERE redwine_id=?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, redWineId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public RedWine findById(int redWineId) { // 根据ID查找红酒
        String sql = "SELECT redwine_id, name, stock, price, country, city, county FROM redwine WHERE redwine_id=?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, redWineId);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                RedWine rw = new RedWine();
                rw.setRedWineId(rs.getInt("redwine_id"));
                rw.setName(rs.getString("name"));
                rw.setStock(rs.getInt("stock"));
                rw.setPrice(rs.getDouble("price"));
                rw.setCountry(rs.getString("country"));
                rw.setCity(rs.getString("city"));
                rw.setCounty(rs.getString("county"));
                return rw;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<RedWine> findAll() { // 查询所有红酒
        List<RedWine> list = new ArrayList<>();
        String sql = "SELECT redwine_id, name, stock, price, country, city, county FROM redwine";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                RedWine rw = new RedWine();
                rw.setRedWineId(rs.getInt("redwine_id"));
                rw.setName(rs.getString("name"));
                rw.setStock(rs.getInt("stock"));
                rw.setPrice(rs.getDouble("price"));
                rw.setCountry(rs.getString("country"));
                rw.setCity(rs.getString("city"));
                rw.setCounty(rs.getString("county"));
                list.add(rw);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<RedWine> findByNameLike(String namePart) {
        List<RedWine> list = new ArrayList<>();
        String sql = "SELECT redwine_id, name, stock, price, country, city, county FROM redwine WHERE name LIKE ?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + namePart + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RedWine rw = new RedWine();
                rw.setRedWineId(rs.getInt("redwine_id"));
                rw.setName(rs.getString("name"));
                rw.setStock(rs.getInt("stock"));
                rw.setPrice(rs.getDouble("price"));
                rw.setCountry(rs.getString("country"));
                rw.setCity(rs.getString("city"));
                rw.setCounty(rs.getString("county"));
                list.add(rw);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<RedWine> findByOrigin(String origin) {
        List<RedWine> list = new ArrayList<>();
        String sql = "SELECT redwine_id, name, stock, price, country, city, county FROM redwine WHERE country=? OR city=? OR county=?";
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, origin);
            pstmt.setString(2, origin);
            pstmt.setString(3, origin);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RedWine rw = new RedWine();
                rw.setRedWineId(rs.getInt("redwine_id"));
                rw.setName(rs.getString("name"));
                rw.setStock(rs.getInt("stock"));
                rw.setPrice(rs.getDouble("price"));
                rw.setCountry(rs.getString("country"));
                rw.setCity(rs.getString("city"));
                rw.setCounty(rs.getString("county"));
                list.add(rw);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<RedWine> findByPriceRange(Double minPrice, Double maxPrice) {
        List<RedWine> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT redwine_id, name, stock, price, country, city, county FROM redwine WHERE 1=1");
        if (minPrice != null) sql.append(" AND price >= ?");
        if (maxPrice != null) sql.append(" AND price <= ?");
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            int idx = 1;
            if (minPrice != null) pstmt.setDouble(idx++, minPrice);
            if (maxPrice != null) pstmt.setDouble(idx++, maxPrice);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RedWine rw = new RedWine();
                rw.setRedWineId(rs.getInt("redwine_id"));
                rw.setName(rs.getString("name"));
                rw.setStock(rs.getInt("stock"));
                rw.setPrice(rs.getDouble("price"));
                rw.setCountry(rs.getString("country"));
                rw.setCity(rs.getString("city"));
                rw.setCounty(rs.getString("county"));
                list.add(rw);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}
